package cn.biplam.common.file.excel;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map.Entry;

import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;

import cn.biplam.common.file.ExcelFileWrite;
import cn.biplam.common.wrapper.ErrorMessage;
import cn.biplam.common.wrapper.KvMap;

public class TestExcel {

	/**
	 * 
	 * @param args
	 * @throws ErrorMessage 
	 */
	public static void main(String[] args) throws ErrorMessage {
		ExcelFileWrite file = new ExcelFileWrite(
		"D:\\探查报告.xls");
		TestExcel te = new TestExcel();
		KvMap<String,ArrayList<String[]>> table = te.getTestObject();
		Iterator<Entry<String,ArrayList<String[]>>> it = table.getIterator();
		ArrayList<String[]> tables;
		String[] row;
		String tableName;
		/**创建目录*/
		String indexName = "目录";
		file.createSheet(indexName);
		/**设置目录标题行高*/
		file.setRowHeight(indexName, 0,30);
		/**写目录标题*/
		te.writeIndexTitle(indexName, file);
		/**表计数器*/
		int index=1;
		while(it.hasNext()){
			Entry<String,ArrayList<String[]>> entry = it.next();
			tables = entry.getValue();
			tableName = entry.getKey();
			/**创建sheet*/
			file.createSheet(tableName);
			/**设置列宽*/
			file.setColumnWidth(tableName, 1, 130);
			file.setColumnWidth(tableName, 2, 130);
			file.setColumnWidth(tableName, 4, 300);
			/**写目录*/
			te.writeIndex(indexName, tableName, file, index);
			for(int i=0;i<tables.size();i++){
				row = tables.get(i);
				if(i==0){
					/**写第一行*/
					te.writeFstRow(row,tableName,file);
				}else if(i==1){
					/**写第二行*/
					te.writeSecRow(row,tableName,file);
				}else if(i==2){
					/**写第三行*/
					te.writeThirdRow(row,tableName,file);
				}else{
					/**写其他行*/
					te.writeOtherRow(row,tableName,i,file);
				}
			}
			index++;
		}

	}
	
	/**
	 * 写目录链接
	 * @param indexName 目录sheet名
	 * @param tableName 表名
	 * @param file 写excel对象
	 * @param index 目录索引序号
	 * @throws ErrorMessage
	 */
	private void writeIndexTitle(String indexName,ExcelFileWrite file) throws ErrorMessage{
		/**表名*/
		file.writeCellMerging(indexName,
				              0,0, 0,11,
				              "数据探查结果说明文档", 
				              getTitleStyle(file).get(), 
				              null,
				              false, 
				              null);
		file.writeCellMerging(indexName,
	              1,1, 0,11,
	              "", 
	              null, 
	              null,
	              false, 
	              null);
	}
	
	/**
	 * 写目录链接
	 * @param indexName 目录sheet名
	 * @param tableName 表名
	 * @param file 写excel对象
	 * @param index 目录索引序号
	 * @throws ErrorMessage
	 */
	private void writeIndex(String indexName,String tableName,ExcelFileWrite file,int index) throws ErrorMessage{
		
		file.writeCellMerging(indexName,
							  index+1,index+1, 0,11,
				              index+". "+tableName , 
				              getLinkStyle(file).get(), 
				              ExcelHyperLink.getHyperLink(Hyperlink.LINK_DOCUMENT, tableName),
				              false, 
				              null);
	}
	
	/**
	 * 写第一行
	 * @param row
	 * @throws ErrorMessage 
	 */
	private void writeFstRow(String[] row,String tableName,ExcelFileWrite file) throws ErrorMessage{
		/**"表名"*/
		file.writeCell(tableName,0, 0, row[0], getBorderStyle(file).get(), null, false, null);
		/**表名*/
		file.writeCellMerging(tableName,0,0, 1,3, row[1], getCommnStyle(file).get(), null, false, null);
		/**表中文名*/
		file.writeCell(tableName,0, 4, row[2], getCommnStyle(file).get(), null, false, null);
	}
	
	/**
	 * 写第二行
	 * @param row
	 * @throws ErrorMessage 
	 */
	private void writeSecRow(String[] row,String tableName,ExcelFileWrite file) throws ErrorMessage{
		/**"说明"*/
		file.writeCell(tableName,1, 0, row[0], getBorderStyle(file).get(), null, false, null);
		/**表说明*/
		file.writeCellMerging(tableName,1,1, 1,4, row[1], getCommnStyle(file).get(), null, false, null);
	}
	
	/**
	 * 写第三行
	 * @param row
	 * @throws ErrorMessage 
	 */
	private void writeThirdRow(String[] row,String tableName,ExcelFileWrite file) throws ErrorMessage{
		/**"主键"*/
		file.writeCell(tableName,2, 0, row[0], getBorderStyle(file).get(), null, false, null);
		/**表主键*/
		file.writeCellMerging(tableName,2,2, 1,4, row[1], getCommnStyle(file).get(), null, false, null);
	}
	
	/**
	 * 写第其他行
	 * @param row
	 * @throws ErrorMessage 
	 */
	private void writeOtherRow(String[] row,String tableName,int rowIdex,ExcelFileWrite file) throws ErrorMessage{
		/**序号*/
		file.writeCell(tableName,rowIdex, 0, row[0], getCommnStyle(file).get(), null, false, null);
		/**列名*/
		file.writeCell(tableName,rowIdex, 1, row[1], getCommnStyle(file).get(), null, false, null);
		/**数据类型*/
		file.writeCell(tableName,rowIdex, 2, row[2], getCommnStyle(file).get(), null, false, null);
		/***/
		file.writeCell(tableName,rowIdex, 3, row[3], getCommnStyle(file).get(), null, false, null);
		/**探查结果*/
		file.writeCell(tableName,rowIdex, 4, row[4], getCommnStyle(file).get(), null, true, getCellStyle(file));
	}
	
	/**
	 * 黑体居中样式
	 * @return
	 */
	private ExcelCellStyle getBorderStyle(ExcelFileWrite file){
		ExcelCellStyle cs = new ExcelCellStyle(file.getHSSFWorkbook());
		/**设置黑体*/
		cs.setFontSize(12);
		cs.setFontName("黑体"); 
		cs.setBorder(HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, 
				HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN);
		/**设置居中*/
		cs.setAlign(HSSFCellStyle.ALIGN_CENTER);
		cs.setVertical(HSSFCellStyle.VERTICAL_CENTER);
		return cs;
	}
	
	/**
	 * 黑体居中样式（标题）
	 * @return
	 */
	private ExcelCellStyle getTitleStyle(ExcelFileWrite file){
		ExcelCellStyle cs = new ExcelCellStyle(file.getHSSFWorkbook());
		/**设置黑体*/
		cs.setFontSize(14);
		cs.setFontName("黑体"); 
		/**设置居中*/
		cs.setAlign(HSSFCellStyle.ALIGN_CENTER);
		cs.setVertical(HSSFCellStyle.VERTICAL_CENTER);
		return cs;
	}
	
	/**
	 * 普通样式
	 * @return
	 */
	private ExcelCellStyle getCommnStyle(ExcelFileWrite file){
		ExcelCellStyle cs = new ExcelCellStyle(file.getHSSFWorkbook());
		cs.setBorder(HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN, 
				HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN);
		cs.setFontName("宋体"); 
		/**设置左对齐*/
		cs.setAlign(HSSFCellStyle.ALIGN_LEFT);
		cs.setVertical(HSSFCellStyle.VERTICAL_CENTER);
		cs.setWrapText(true);
		return cs;
	}
	
	/**
	 * 普通样式
	 * @return
	 */
	private ExcelCellStyle getLinkStyle(ExcelFileWrite file){
		ExcelCellStyle cs = new ExcelCellStyle(file.getHSSFWorkbook());
		cs.setFontName("宋体");
		cs.setFontColor(HSSFColor.BLUE.index);
		cs.setFontUnderline(HSSFFont.U_SINGLE);
		/**设置左对齐*/
		cs.setAlign(HSSFCellStyle.ALIGN_LEFT);
		cs.setVertical(HSSFCellStyle.VERTICAL_CENTER);
		cs.setWrapText(true);
		return cs;
	}
	
	/**
	 * 设置同一单元格的不同颜色样式
	 * @return
	 */
	private ArrayList<Object[]> getCellStyle(ExcelFileWrite file){
		ArrayList<Object[]> al = new ArrayList<Object[]>(); 
		Object[] fontStyleRed = new Object[2];
		
		/**以&标识的字体，红色*/
		ExcelFont fontRed = new ExcelFont(file.getHSSFWorkbook());
		fontRed.setFontColor(HSSFColor.RED.index);
		fontStyleRed[0]="&";
		fontStyleRed[1]=fontRed.getHSSFFont();
		al.add(fontStyleRed);
		/**以#标识的字体，蓝色*/
		Object[] fontStyleBule = new Object[2];
		ExcelFont fontBule = new ExcelFont(file.getHSSFWorkbook());
		fontBule.setFontColor(HSSFColor.BLUE.index);
		fontStyleBule[0]="#";
		fontStyleBule[1]=fontBule.getHSSFFont();
		al.add(fontStyleBule);
		
		return al;
	}
	
	private KvMap<String,ArrayList<String[]>> getTestObject() throws ErrorMessage{
		KvMap<String,ArrayList<String[]>> table = new KvMap<String, ArrayList<String[]>>();
		table.put("R_CONT_MAIN", getTestObjectTable());
		table.put("R_CONT_MAIN1", getTestObjectTable());
		return table;
	}
	
	private ArrayList<String[]> getTestObjectTable(){
		
		ArrayList<String[]> table = new ArrayList<String[]>();
		/**第一行*/
		String[] row5 = new String[3];
		row5[0] = "名称";
		row5[1] = "R_CONT_MAIN";
		row5[2] = "合约主表";
		/**第二行*/
		String[] row6 = new String[2];
		row6[0] = "说明";
		row6[1] = "";
		/**第三行*/
		String[] row7 = new String[2];
		row7[0] = "主键";
		row7[1] = "";
		table.add(row5);
		table.add(row6);
		table.add(row7);
		
		/**第一行*/
		String[] row1 = new String[5];
		row1[0] = "1";
		row1[1] = "N_EXPI_PREP";
		row1[2] = "NUMBER(10,6)";
		row1[3] = "Y";
		row1[4] = "未满期保费比例\n\rDefault 0.3000\n\r&空值泛查:空值探查&";
		
		/**第二行*/
		String[] row2 = new String[5];
		row2[0] = "2";
		row2[1] = "N_ID";
		row2[2] = "INT";
		row2[3] = "Y";
		row2[4] = "标识\n\rPK从seq_cont_id分配\n\r#空值泛查:空值探查(最大比例20.0%),通过.记录数：15#";
		
		/**第三行*/
		String[] row3 = new String[5];
		row3[0] = "3";
		row3[1] = "C_BEGIN_DATE";
		row3[2] = "CHAR(18)";
		row3[3] = "Y";
		row3[4] = "起始日期\n\r代码字段泛查:\n\r缺失代码:\n\r&20080701 记录数=1\n\r20070101 记录数=3\n\r20080101 记录数=4\n\r" +
				"20070801 记录数=1\n\r20090101 记录数=5&\n\r#包含代码: \n\r2008071 1#\n\r&空值泛查,未通过&lskjdflak&slkjfouslj&\n\r#数据范围通过#";
		
		/**第四行*/
		String[] row4 = new String[5];
		row4[0] = "4";
		row4[1] = "T_APP_TM";
		row4[2] = "DATE";
		row4[3] = "Y";
		row4[4] = "投保日期\n\r#日期范围泛查:数据范围满足[to_date('2005-01-01','yyyy-mm-dd')]-[to_date('2051-01-01','yyyy-mm-dd')]#";
		
		table.add(row1);
		table.add(row2);
		table.add(row3);
		table.add(row4);
		return table;
	}
}
